﻿using System;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections.Generic;

namespace DMZS.DAL
{
    /// <summary>
    /// 采购订单
    /// </summary>
    public class SalesOrder : DlyndxOrder
    {

        /// <summary>
        /// 获取数据 对话框
        /// </summary>
        /// <param name="code"></param>
        /// <param name="status">0:全部; 1:待审; 2:已审; 3:关闭</param>
        /// <param name="dateIndex">0:近七天; 1:本周; 2:近一月; 3:本月; 4:全部; 5:全部</param>
        public DataSet GetListForDialog(string code, int status, int dateIndex)
        {
            var strSql = @" 
select * from vSalesOrder 
where Number like '%'+@code+'%' and {0} and {1}
order by Vchcode desc";

            var strDate = string.Empty;
            int intStatus;
            var date = DateTime.Now.Date;
            switch (dateIndex)
            {
                case 0:
                    strDate += "convert(nvarchar,date,112)>=convert(nvarchar, dateadd(day,-7,getdate()), 112)";
                    break;
                case 1:
                    var day = date.DayOfWeek;
                    var addDay = day == DayOfWeek.Sunday ? 6 : Convert.ToInt32(day) - 1;
                    strDate += "convert(nvarchar,date,112)>='" + string.Format("{0:yyyyMMdd}", date.AddDays(-1 * addDay)) + "'";
                    break;
                case 2:
                    strDate += "convert(nvarchar,date,112)>=convert(nvarchar, dateadd(day,-30,getdate()), 112)";
                    break;
                case 3:
                    strDate += "convert(nvarchar,date,112)>='" + string.Format("{0:yyyyMMdd}", date.AddDays(1 - date.Day)) + "'";

                    break;
                case 4:
                    strDate += "1=1";
                    break;
                default:
                    strDate += "1=1";
                    break;
            }
            switch (status)
            {
                case 0:
                    intStatus = -1;
                    break;
                case 1:
                    intStatus = 0;
                    break;
                case 2:
                    intStatus = 1;
                    break;
                case 3:
                    intStatus = 2;
                    break;
                default:
                    intStatus = -1;
                    break;
            }

            if (intStatus == -1)
            {
                strSql = string.Format(strSql, strDate, "1=1");
            }
            else
            {
                strSql = string.Format(strSql, strDate, "draft=@draft");
            }

            var parameters = new SqlParameter[] { new SqlParameter("@code", SqlDbType.VarChar, 40),
                    new SqlParameter("@draft", SqlDbType.Int, 4) };
            parameters[0].Value = code;
            parameters[1].Value = intStatus;

            return DbHelperSQL.Query(strSql, parameters);
        }

        /// <summary>
        /// 获取头部
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public override DataRow GetHead(long ID)
        {
            var strSql = @"
select 
	h.Vchcode,h.Date,h.Number,h.VchType,h.Comment,
	h.btypeid,h.etypeid,h.ktypeid,h.ktypeid2,h.ifcheck,
	h.checke,h.Period,h.InputNo,h.draft,h.deptid,
	h.IsStop,h.todate,h.defdiscount,h.checkeDate,h.creator,
	s.Code ClientCode,s.Name ClientName,
	w.Code WarehouseCode,w.Name WarehouseName,
    d.Code DepartmentCode,d.Name DepartmentName
from DlyndxOrder h
join Vchtype v on v.vchtype=h.VchType
left join DepartmentNew d on d.ID=h.deptid
left join Supplier s on s.ID=h.btypeid
left join Warehouse w on w.ID=h.ktypeid
where h.Vchcode=@ID --and v.vCode='CGDD'
";
            var parameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.BigInt, 8)
            };
            parameters[0].Value = ID;

            var ds = DbHelperSQL.Query(strSql, parameters);

            if (ds == null || ds.Tables[0].Rows.Count == 0)
            {
                return null;
            }
            return ds.Tables[0].Rows[0];
        }

        /// <summary>
        /// 获取表体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public override DataTable GetBody(long ID)
        {
            //            var strSql = @"
            //select 
            //	h.Vchcode,h.Date,h.Number,h.VchType,h.Comment,
            //	h.btypeid,h.etypeid,h.ktypeid,h.ktypeid2,h.ifcheck,
            //	h.checke,h.Period,h.InputNo,h.draft,h.deptid,
            //	h.IsStop,h.todate,h.defdiscount,h.checkeDate,h.creator,
            //	b.DlyOrder,b.PtypeId,b.Qty,b.unit,b.Price,(b.Qty*b.Price) SubTotal,
            //	b.unit1,b.qty1,b.comment BodyComment,b.qtytotal,b.Date BodyDate,
            //	s.Code ClientCode,s.Name ClientName,
            //	w.Code WarehouseCode,w.Name WarehouseName,
            //	u.BaseCode UnitBaseCode, u.BaseName UnitBaseName,
            //    p.Code ProductCode,p.Name ProductName
            //from DlyndxOrder h
            //join DlySale b on b.Vchcode=h.Vchcode
            //join Vchtype v on v.vchtype=h.VchType
            //left join Product p on p.ID=b.PtypeId
            //left join Supplier s on s.ID=h.btypeid
            //left join Warehouse w on w.ID=h.ktypeid
            //left join UnitBase u on u.UnitBaseID=b.unit1
            //where h.Vchcode=@ID --and v.vCode='CGDD'
            //";
            var strSql = "select * from vSalesOrderLines where Vchcode=@ID";
            var parameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.BigInt, 8)
            };
            parameters[0].Value = ID;

            var ds = DbHelperSQL.Query(strSql, parameters);

            if (ds == null)
            {
                return null;
            }
            return ds.Tables[0];
        }
        public DataTable GetBody(string Code)
        {
            //            var strSql = @"
            //select 
            //	h.Vchcode,h.Date,h.Number,h.VchType,h.Comment,
            //	h.btypeid,h.etypeid,h.ktypeid,h.ktypeid2,h.ifcheck,
            //	h.checke,h.Period,h.InputNo,h.draft,h.deptid,
            //	h.IsStop,h.todate,h.defdiscount,h.checkeDate,h.creator,
            //	b.DlyOrder,b.PtypeId,b.Qty,b.unit,b.Price,(b.Qty*b.Price) SubTotal,
            //	b.unit1,b.qty1,b.comment BodyComment,b.qtytotal,b.Date BodyDate,
            //	s.Code SupplierCode,s.Name SupplierName,
            //	w.Code WarehouseCode,w.Name WarehouseName,
            //	u.BaseCode UnitBaseCode, u.BaseName UnitBaseName,
            //    p.Code ProductCode,p.Name ProductName,
            //    v.vname VchTypeName
            //from DlyndxOrder h
            //join DlySale b on b.Vchcode=h.Vchcode
            //join Vchtype v on v.vchtype=h.VchType
            //left join Product p on p.ID=b.PtypeId
            //left join Supplier s on s.ID=h.btypeid
            //left join Warehouse w on w.ID=h.ktypeid
            //left join UnitBase u on u.UnitBaseID=b.unit1
            //where h.Number=@Number and v.vCode='CGDD'
            //";
            var strSql = "select * from vSalesOrderLines where Number=@Number";
            var parameters = new SqlParameter[] { new SqlParameter("@ID", SqlDbType.BigInt, 8)
            };
            parameters[0].Value = Code;

            var ds = DbHelperSQL.Query(strSql, parameters);

            if (ds == null)
            {
                return null;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public long AddExpand(DMZS.Model.SalesOrder model)
        {
            var list_sql = new List<string>();
            var list_parm = new List<SqlParameter[]>();

            var strSql_head = @"
--单据类型
if not exists(select count(1) from Vchtype where vchtype=@VchType)
begin
    select -10021
    return
end
--供应商 
if @btypeid<>0 and ( not exists(select count(1) from Supplier where ID=@btypeid) )
begin
    select -10022
    return
end

--部门
if not exists(select count(1) from DepartmentNew where ID=@deptid)
begin
    select -10023
    return
end

insert into DlyndxOrder(
    Date,Number,VchType,Comment,btypeid,etypeid,ktypeid,ifcheck,checke,Period,InputNo,draft,deptid,IsStop,todate,defdiscount,checkeDate,creator)
values (
    @Date,@Number,@VchType,@Comment,@btypeid,@etypeid,@ktypeid,@ifcheck,@checke,@Period,@InputNo,@draft,@deptid,@IsStop,@todate,@defdiscount,@checkeDate,@creator)
;

if object_id('tempdb..##tempTable') is not null begin
    drop table ##tempTable
end
create table ##tempTable(ID bigint)

insert into ##tempTable(ID) values(@@IDENTITY)
select ID from ##tempTable 
";
            var parameters_head = new SqlParameter[] { new SqlParameter("@Date", SqlDbType.DateTime),
                    new SqlParameter("@Number", SqlDbType.VarChar, 40),
                    new SqlParameter("@VchType", SqlDbType.Decimal, 5),
                    new SqlParameter("@Comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@btypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@etypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ktypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ifcheck", SqlDbType.Char, 1),
                    new SqlParameter("@checke", SqlDbType.VarChar, 25),
                    new SqlParameter("@Period", SqlDbType.SmallInt, 2),
                    new SqlParameter("@InputNo", SqlDbType.VarChar, 25),
                    new SqlParameter("@draft", SqlDbType.Int, 4),
                    new SqlParameter("@deptid", SqlDbType.VarChar, 25),
                    new SqlParameter("@IsStop", SqlDbType.Int, 4),
                    new SqlParameter("@todate", SqlDbType.DateTime),
                    new SqlParameter("@defdiscount", SqlDbType.Decimal, 9),
                    new SqlParameter("@checkeDate", SqlDbType.DateTime),
                    new SqlParameter("@creator", SqlDbType.VarChar, 25) };
            parameters_head[0].Value = model.Date;
            parameters_head[1].Value = model.Number;
            parameters_head[2].Value = model.VchType;
            parameters_head[3].Value = model.Comment;
            parameters_head[4].Value = model.btypeid;
            parameters_head[5].Value = model.etypeid;
            parameters_head[6].Value = model.ktypeid;
            parameters_head[7].Value = model.ifcheck;
            parameters_head[8].Value = model.checke;
            parameters_head[9].Value = model.Period;
            parameters_head[10].Value = model.InputNo;
            parameters_head[11].Value = model.draft;
            parameters_head[12].Value = model.deptid;
            parameters_head[13].Value = model.IsStop;
            if (model.todate == DateTime.MinValue)
            {
                parameters_head[14].Value = DBNull.Value;
            }
            else
            {
                parameters_head[14].Value = model.todate;
            }
            parameters_head[15].Value = model.defdiscount;
            parameters_head[16].Value = DBNull.Value;
            parameters_head[17].Value = model.Creator;


            list_sql.Add(strSql_head);
            list_parm.Add(parameters_head);

            foreach (DataRow row in model.Tbody.Rows)
            {
                var strSql_body = @"
--产品
if not exists(select count(1) from Product where Code=@PtypeId)
begin
    select 1    --跳过
    return
end

declare @Vchcode bigint
select @Vchcode=ID from ##tempTable 

insert into DlySale(Vchcode,PtypeId,Qty,unit,unit1,qty1,comment,qtytotal,Date,Price) 
values(@Vchcode,@PtypeId,@Qty,@unit,@unit1,@qty1,@comment,@qtytotal,@Date,@Price)
select @@identity
";
                var parameters_body = new SqlParameter[] { new SqlParameter("@PtypeId", SqlDbType.VarChar, 25),
                    new SqlParameter("@Qty", SqlDbType.Int, 4),
                    new SqlParameter("@unit", SqlDbType.Int, 4),
                    new SqlParameter("@unit1", SqlDbType.Int, 4),
                    new SqlParameter("@qty1", SqlDbType.Int, 4),
                    new SqlParameter("@comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@qtytotal", SqlDbType.Int, 4),
                    new SqlParameter("@Date", SqlDbType.DateTime),
                    new SqlParameter("@Price", SqlDbType.Decimal) };
                parameters_body[0].Value = row["PtypeId"];
                parameters_body[1].Value = row["Qty"];
                parameters_body[2].Value = row["unit"];
                parameters_body[3].Value = row["unit"];
                parameters_body[4].Value = row["qty1"];
                parameters_body[5].Value = row["comment"];
                parameters_body[6].Value = row["Qty"];
                parameters_body[7].Value = DateTime.Now;
                parameters_body[8].Value = row["Price"];

                list_sql.Add(strSql_body);
                list_parm.Add(parameters_body);
            }

            var strSql_end = @"
declare @ID bigint
select @ID=ID from ##tempTable
drop table ##tempTable
select @ID";
            list_sql.Add(strSql_end);
            list_parm.Add(null);

            var result = string.Empty;
            DbHelperSQL.ExecuteSqlTran(list_sql, list_parm, ref result);
            return Convert.ToInt64(result);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public long UpdateExpand(DMZS.Model.SalesOrder model)
        {
            var list_sql = new List<string>();
            var list_parm = new List<SqlParameter[]>();

            var strSql_head = @"
if not exists(select count(1) from Vchtype where vchtype=@VchType)
begin
    select -10021
    return
end
--供应商 
if @btypeid<>0 and ( not exists(select count(1) from Supplier where ID=@btypeid) )
begin
    select -10022
    return
end

--部门
if not exists(select count(1) from DepartmentNew where ID=@deptid)
begin
    select -10023
    return
end

update DlyndxOrder set
    --Date=@Date,
    --Number=@Number,
    --VchType=@VchType,
    Comment=@Comment,
    btypeid=@btypeid,
    --etypeid=@etypeid,
    ktypeid=@ktypeid,
    --ktypeid2=@ktypeid2,
    --ifcheck=@ifcheck,
    --checke=@checke,
    --Period=@Period,
    InputNo=@InputNo,
    --draft=@draft,
    deptid=@deptid,
    --IsStop=@IsStop,
    todate=@todate,
    defdiscount=@defdiscount
   -- checkeDate=@checkeDate
where Vchcode=@Vchcode

if object_id('tempdb..##tempTable') is not null begin
    drop table ##tempTable
end

select @Vchcode ID into ##tempTable
select ID from ##tempTable
";

            var parameters_head = new SqlParameter[] { new SqlParameter("@Date", SqlDbType.DateTime),
                    new SqlParameter("@Number", SqlDbType.VarChar, 40),
                    new SqlParameter("@VchType", SqlDbType.Decimal, 5),
                    new SqlParameter("@Comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@btypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@etypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ktypeid", SqlDbType.VarChar, 25),
                    new SqlParameter("@ifcheck", SqlDbType.Char, 1),
                    new SqlParameter("@checke", SqlDbType.VarChar, 25),
                    new SqlParameter("@Period", SqlDbType.SmallInt, 2),
                    new SqlParameter("@InputNo", SqlDbType.VarChar, 25),
                    new SqlParameter("@draft", SqlDbType.Int, 4),
                    new SqlParameter("@deptid", SqlDbType.VarChar, 25),
                    new SqlParameter("@IsStop", SqlDbType.Int, 4),
                    new SqlParameter("@todate", SqlDbType.DateTime),
                    new SqlParameter("@defdiscount", SqlDbType.Decimal, 9),
                    new SqlParameter("@checkeDate", SqlDbType.DateTime),
                    new SqlParameter("@creator", SqlDbType.VarChar, 25),
                    new SqlParameter("@Vchcode", SqlDbType.Decimal, 9) };
            parameters_head[0].Value = model.Date;
            parameters_head[1].Value = model.Number;
            parameters_head[2].Value = model.VchType;
            parameters_head[3].Value = model.Comment;
            parameters_head[4].Value = model.btypeid;
            parameters_head[5].Value = model.etypeid;
            parameters_head[6].Value = model.ktypeid;
            parameters_head[7].Value = model.ifcheck;
            parameters_head[8].Value = model.checke;
            parameters_head[9].Value = model.Period;
            parameters_head[10].Value = model.InputNo;
            parameters_head[11].Value = model.draft;
            parameters_head[12].Value = model.deptid;
            parameters_head[13].Value = model.IsStop;
            if (model.todate == DateTime.MinValue)
            {
                parameters_head[14].Value = DBNull.Value;
            }
            else
            {
                parameters_head[14].Value = model.todate;
            }
            parameters_head[15].Value = model.defdiscount;
            parameters_head[16].Value = DBNull.Value;
            parameters_head[17].Value = model.Creator;
            parameters_head[18].Value = model.Vchcode;


            list_sql.Add(strSql_head);
            list_parm.Add(parameters_head);

            var str_id_now = ",0";
            foreach (DataRow row in model.Tbody.Rows)
            {
                if (row["DlyOrder"] != DBNull.Value && row["DlyOrder"].ToString() != string.Empty)
                {
                    str_id_now += "," + row["DlyOrder"].ToString();
                }
            }

            var strSql_body_delete = @"
declare @Vchcode bigint
select @Vchcode=ID from ##tempTable 

delete DlySale where Vchcode=@Vchcode and (DlyOrder not in({0}))
select 1 
";
            strSql_body_delete = string.Format(strSql_body_delete, str_id_now.Substring(1));

            list_sql.Add(strSql_body_delete);
            list_parm.Add(null);

            foreach (DataRow row in model.Tbody.Rows)
            {
                string strSql_body;
                if (row["DlyOrder"] == DBNull.Value || row["DlyOrder"].ToString() == string.Empty)
                {
                    strSql_body = @"
--产品
if not exists(select count(1) from Product where Code=@PtypeId)
begin
    select 1    --跳过
    return
end

declare @Vchcode bigint
select @Vchcode=ID from ##tempTable 

insert into DlySale(Vchcode,PtypeId,Qty,unit,unit1,qty1,comment,qtytotal,Date,Price) 
values(@Vchcode,@PtypeId,@Qty,@unit,@unit1,@qty1,@comment,@qtytotal,@Date,@Price)
select @@identity
";
                }
                else
                {
                    strSql_body = @"
--产品
if not exists(select count(1) from Product where Code=@PtypeId)
begin
    select 1    --跳过
    return
end

declare @Vchcode bigint
select @Vchcode=ID from ##tempTable 

update DlySale set
   --Vchcode=@Vchcode,
   PtypeId=@PtypeId,
   Qty=@Qty,
   unit=@unit,
   qty1=@qty1,
   qtytotal=qtytotal,
   Date=@Date,
   Price=@Price
where DlyOrder=@DlyOrder
select @DlyOrder
";
                }

                var parameters_body = new SqlParameter[] { new SqlParameter("@PtypeId", SqlDbType.VarChar, 25),
                    new SqlParameter("@Qty", SqlDbType.Int, 4),
                    new SqlParameter("@unit", SqlDbType.Int, 4),
                    new SqlParameter("@unit1", SqlDbType.Int, 4),
                    new SqlParameter("@qty1", SqlDbType.Int, 4),
                    new SqlParameter("@comment", SqlDbType.VarChar, 256),
                    new SqlParameter("@qtytotal", SqlDbType.Int, 4),
                    new SqlParameter("@Date", SqlDbType.DateTime),
                    new SqlParameter("@Price", SqlDbType.Decimal),
                    new SqlParameter("@DlyOrder", SqlDbType.BigInt, 8) };
                parameters_body[0].Value = row["PtypeId"];
                parameters_body[1].Value = row["Qty"];
                parameters_body[2].Value = row["unit"];
                parameters_body[3].Value = row["unit"];
                parameters_body[4].Value = row["qty1"];
                parameters_body[5].Value = row["comment"];
                parameters_body[6].Value = row["Qty"];
                parameters_body[7].Value = DateTime.Now;
                parameters_body[8].Value = row["Price"];
                parameters_body[9].Value = row["DlyOrder"];


                list_sql.Add(strSql_body);
                list_parm.Add(parameters_body);
            }


            var result = string.Empty;
            DbHelperSQL.ExecuteSqlTran(list_sql, list_parm, ref  result);
            return Convert.ToInt64(result);
        }
    }
}
